package br.com.skytef.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpSession;

import br.com.skytef.entity.PosAutorizadoEntity;
import br.com.skytef.jdbc.ConnectionFactory;
import br.com.skytef.utils.utils;

public class PosAutoridadoDao {
	public Connection connection;

	@SuppressWarnings("static-access")
	public PosAutoridadoDao() {
		this.connection = new ConnectionFactory().getConnection();
	}

	public PosAutoridadoDao(HttpSession session) {
		this.connection = utils.recoverSessionDBConnection(session);
	}

	public PosAutoridadoDao(Connection con) {
		this.connection = con;
	}

	public Connection getConnection() {
		return connection;
	}
	
	protected void finalize() throws Throwable{
		if(connection != null)
			if(connection.isClosed() == true)
				connection.close();
		connection = null;
		super.finalize();
	} 

	/*--------------------------------------------------------------------------------------------------------
	 * Insert
	 --------------------------------------------------------------------------------------------------------*/
	public PosAutorizadoEntity insert(PosAutorizadoEntity pos) throws Exception {
		try {
	
			PreparedStatement stmt = connection.prepareStatement(this.getQryInsert());
			stmt.setString(1, pos.getSerie());
//			stmt.setString(2, pos.getModelo());
//			stmt.setString(3, pos.getConexao());
			stmt.setBoolean(2, pos.isAtivo());
//			stmt.setBoolean(5, pos.isNavs());
//			stmt.setBoolean(6, pos.isOs());
			stmt.setBoolean(3, pos.isEos());
			stmt.setDate(4, pos.getDataInclusao());

			stmt.execute();
			stmt.close();

			
		} catch (SQLException e) {
			throw new Exception(e);
		} catch (Exception e) {
			throw new Exception(e);
		}
		return pos;
	}

	/*--------------------------------------------------------------------------------------------------------
	 * update
	 --------------------------------------------------------------------------------------------------------*/
	public PosAutorizadoEntity update(PosAutorizadoEntity pos) throws Exception {
		try {
			PreparedStatement stmt = connection.prepareStatement(this.getQryUpdate());
			stmt.setString(1, pos.getSerie());
//			stmt.setString(2, pos.getModelo());
//			stmt.setString(3, pos.getConexao());
			stmt.setBoolean(2, pos.isAtivo());
//			stmt.setBoolean(5, pos.isNavs());
//			stmt.setBoolean(6, pos.isOs());
			stmt.setBoolean(3, pos.isEos());
			stmt.setDate(4, pos.getDataAlteracao());
			stmt.setInt(5, pos.getId());
			
			
			stmt.execute();
			stmt.close();

		} catch (SQLException e) {
			throw new Exception(e);
		} catch (Exception e) {
			throw new Exception(e);
		}
		
		return pos;
	}
	
	/*--------------------------------------------------------------------------------------------------------
	 * Select
	 --------------------------------------------------------------------------------------------------------*/
	
	public  List<PosAutorizadoEntity> searchAllWeb() throws SQLException{
		Connection connection = new ConnectionFactory().getConnection();
		try {
			List<PosAutorizadoEntity> posList = new ArrayList<PosAutorizadoEntity>();

			PreparedStatement stmt = connection.prepareStatement(this.getQrySelectAll());
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				PosAutorizadoEntity posAut = new PosAutorizadoEntity();
				
				posAut.setId(rs.getInt("id"));
				posAut.setSerie(rs.getString("serie"));
//				posAut.setModelo(rs.getString("Modelo"));
//				posAut.setConexao(rs.getString("conexao"));
				posAut.setAtivo(rs.getBoolean("ativo"));
//				posAut.setNavs(rs.getBoolean("navs"));
//				posAut.setOs(rs.getBoolean("os"));
				posAut.setEos(rs.getBoolean("eos"));
				posAut.setDataInclusao(rs.getDate("dataInclusao"));
				posAut.setDataAlteracao(rs.getDate("dataAlteracao"));
				posList.add(posAut);
			} 

			rs.close();
			stmt.close();
			connection.close();
			return posList;

		} catch (SQLException e) {
			connection.rollback();
			connection.close();
			throw new RuntimeException(e);
		}
		
	}
	
	
	
	
	public PosAutorizadoEntity selectAll() throws Exception {
		Connection connection = new ConnectionFactory().getConnection();
		PosAutorizadoEntity result = null;
		try {
			PreparedStatement stmt = connection.prepareStatement(this.getQrySelectAll());
			result = this.execSelect(stmt);
			stmt.close();
			connection.close();	
		} catch (Exception e) {
			connection.close();
		}		
		return result;
	} 
	
	public PosAutorizadoEntity CountAll(Boolean situacao) throws Exception {
		Connection connection = new ConnectionFactory().getConnection();
		PosAutorizadoEntity result = null;
		try {
			PreparedStatement stmt = connection.prepareStatement(this.getQryCountAll());
			stmt.setBoolean(1, situacao);
			result = this.execSelectCount(stmt);
			stmt.close();
			connection.close();	
		} catch (Exception e) {
			connection.close();
		}		
		return result;
	} 
	
	
	
	public PosAutorizadoEntity selectSerie(String serie) throws Exception {
		Connection connection = new ConnectionFactory().getConnection();
		PreparedStatement stmt = connection.prepareStatement(this.getQrySelectSerie());
		stmt.setString(1, serie);
		PosAutorizadoEntity result = this.execSelect(stmt);
		stmt.close();
		connection.close();
		return result;
	} 
	
	public PosAutorizadoEntity selectId(int id) throws Exception {
		Connection connection = new ConnectionFactory().getConnection();
		PreparedStatement stmt = connection.prepareStatement(this.getQrySelectId());
		stmt.setInt(1, id);
		PosAutorizadoEntity result = this.execSelect(stmt);
		stmt.close();
		connection.close();
		return result;
	} 
	
	private PosAutorizadoEntity execSelect(PreparedStatement stmt) throws Exception {
		PosAutorizadoEntity posAut = new PosAutorizadoEntity();

		try {
			ResultSet rs = stmt.executeQuery();
			if (rs.next()) {				
				posAut.setId(rs.getInt("id"));
				posAut.setSerie(rs.getString("serie"));
//				posAut.setModelo(rs.getString("Modelo"));
//				posAut.setConexao(rs.getString("conexao"));
				posAut.setAtivo(rs.getBoolean("ativo"));
//				posAut.setNavs(rs.getBoolean("navs"));
//				posAut.setOs(rs.getBoolean("os"));
				posAut.setEos(rs.getBoolean("eos"));
				posAut.setDataInclusao(rs.getDate("dataInclusao"));
				posAut.setDataAlteracao(rs.getDate("dataAlteracao"));
			}
			rs.close();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		}
		return posAut;
	}
	
	private PosAutorizadoEntity execSelectCount(PreparedStatement stmt) throws Exception {
		PosAutorizadoEntity posAut = new PosAutorizadoEntity();

		try {
			ResultSet rs = stmt.executeQuery();
			if (rs.next()) {				
				posAut.setTotal(rs.getInt("Total"));
			}
			rs.close();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		}
		return posAut;
	}

	/*--------------------------------------------------------------------------------------------------------
	 * Query
	 --------------------------------------------------------------------------------------------------------*/

	private String getQryInsert() {

		StringBuffer sql = new StringBuffer();

		sql.append("INSERT INTO pos_autorizado ");
		sql.append("             (serie, ");
		sql.append("             ativo, ");
		sql.append("             eos, ");
		sql.append("             dataInclusao ) ");
		sql.append("VALUES      (?, ?, ?, ? ); ");

		return sql.toString();

	}

	private String getQryUpdate() {

		StringBuffer sql = new StringBuffer();

		sql.append("UPDATE pos_autorizado ");
		sql.append("SET    serie = ?, ");
		sql.append("       ativo = ?, ");
		sql.append("       eos = ?, ");
		sql.append("       dataAlteracao = ? ");	
		sql.append("WHERE  id = ?; ");

		return sql.toString();

	}

	private String getQrySelectId() {

		StringBuffer sql = new StringBuffer();

		sql.append("SELECT pos_autorizado.id, ");
		sql.append("       pos_autorizado.serie, ");
		sql.append("       pos_autorizado.ativo, ");
		sql.append("       pos_autorizado.eos, ");
		sql.append("       pos_autorizado.dataInclusao, ");
		sql.append("       pos_autorizado.dataAlteracao ");
		sql.append("FROM   pos_autorizado ");
		sql.append("WHERE  id = ?; ");
		return sql.toString();

	}
	
	private String getQrySelectSerie() {

		StringBuffer sql = new StringBuffer();

		sql.append("SELECT pos_autorizado.id, ");
		sql.append("       pos_autorizado.serie, ");
		sql.append("       pos_autorizado.ativo, ");
		sql.append("       pos_autorizado.eos, ");
		sql.append("       pos_autorizado.dataInclusao, ");
		sql.append("       pos_autorizado.dataAlteracao ");
		sql.append("FROM   pos_autorizado ");
		sql.append("WHERE  serie = ?; ");
		return sql.toString();

	}
	
	
	private String getQrySelectAll() {

		StringBuffer sql = new StringBuffer();

		sql.append("SELECT pos_autorizado.id, ");
		sql.append("       pos_autorizado.serie, ");
		sql.append("       pos_autorizado.ativo, ");
		sql.append("       pos_autorizado.eos, ");
		sql.append("       pos_autorizado.dataInclusao, ");
		sql.append("       pos_autorizado.dataAlteracao ");
		sql.append("FROM   pos_autorizado  ;");
		return sql.toString();

	}
	
	private String getQryCountAll() {

		StringBuffer sql = new StringBuffer();

		sql.append("SELECT Count(pos_autorizado.id) as Total ");
		sql.append("FROM   pos_autorizado  ");
		sql.append("WHERE  pos_autorizado.ativo = ? ;");
		return sql.toString();

	}

	private String getQryDelete() {

		StringBuffer sql = new StringBuffer();

		sql.append("DELETE FROM pos_autorizado ");
		sql.append("WHERE  id = ?; ");
		return sql.toString();

	}
}
